package app.beit.fintrack.dao.summarytrack;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import app.beit.fintrack.dao.BaseDao;
import app.beit.fintrack.entity.summary.SummaryTrackAppeal;
import app.beit.fintrack.entity.summary.SummaryTrackByStatus;
import app.beit.fintrack.entity.summary.SummaryTrackChart;
import app.beit.fintrack.entity.summary.SummaryTrackDetailsData;
import app.beit.fintrack.entity.summary.SummaryTrackPidData;
import app.beit.fintrack.entity.summary.SummaryZone;
import app.beit.fintrack.utils.DateFormatUtils;
import app.beit.fintrack.utils.enums.ESearchBy;
import app.beit.fintrack.utils.enums.ESearchByStatus;

public class bak_SummaryTrackDao extends BaseDao {

	private final Logger log = Logger.getLogger(this.getClass());

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getAllInEClaimByDateSend(
			final String hospitalCode, final Date fromDate, final Date toDate,
			final SummaryZone zone) throws Exception {
		this.log.info("get repno All in E-Claim by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date : "
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select distinct(s.repno) as repNo, 0 as amount, 0 as total, 0 as sumsServiceItem from m_seamless s ");
		sb.append("where s.datesend >= to_date('")
				.append(DateFormatUtils.dateToStr(fromDate))
				.append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('")
				.append(DateFormatUtils.dateToStr(toDate))
				.append("', 'dd/MM/yyyy') ");
		if (!hospitalCode.equalsIgnoreCase("")) {
			sb.append("and s.hospitalCode='").append(hospitalCode).append("' ");
		}
		sb.append("and s.zoneid='").append(zone.getZoneId()).append("' ");
		sb.append("order by s.repno");
		this.log.info("get repno All in E-Claim with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno All in E-Claim.......");

		return results;
	}

	@SuppressWarnings("unchecked")
	public synchronized List<SummaryZone> getSumTotalProcess(
			final Date fromDate, final Date toDate) throws Exception {
		this.log.info("get summary total from e-claim from date : " + fromDate
				+ " to date : " + toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName, amount ");
		sb.append("from province p left join (select zoneId, sum(amount) as amount from( ");
		sb.append("select s.zoneid as zoneId, sum(s.amount) as amount from m_seamless s where s.status in ('B', 'E') ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("group by s.zoneid ");
		sb.append("	union 	");
		sb.append("select s.zoneid as zoneId, sum(s.charge_cost) as amount from m_seamless s where s.status='C' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("group by s.zoneid) ");
		sb.append("group by zoneId) ");
		sb.append("on p.nhso_zone=zoneid where p.nhso_zone < 14 ");
		sb.append("group by p.nhso_zone, p.nhso_zonename , amount ");
		sb.append("order by p.nhso_zone ");

		this.log.info("get summary total from e-claim with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryZone.class);
		final List<SummaryZone> results = sql.list();
		session.close();

		this.log.info("end get summary total from e-claim............");

		return results;
	}

	@SuppressWarnings("unchecked")
	public synchronized List<SummaryZone> getSumTotalPayable(
			final Date fromDate, final Date toDate) throws Exception {
		this.log.info("get summary total from sapbo payable from date : "
				+ fromDate + " to date : " + toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName, amount ");
		sb.append("from province p left join (select zoneId, sum(amount) as amount from( ");
		sb.append("select s.zoneid as zoneId, sum(s.amount) as amount from m_seamless s where s.status='P' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("group by s.zoneid ");
		sb.append("	union	");
		sb.append("select s.zoneid as zoneId, sum(s.amount) as amount from m_seamless s where s.status='T' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.repno in (select re.ref_doc_no from ods_dw_regup re ");
		sb.append("where re.idct_prop_run<>'X' and re.pmnt_doc_no<>' ' and trunc(re.run_dt)>trunc(sysdate))");
		sb.append("group by s.zoneid) ");
		sb.append("group by zoneId) ");
		sb.append("on p.nhso_zone=zoneid where p.nhso_zone < 14 ");
		sb.append("group by p.nhso_zone, p.nhso_zonename , amount ");
		sb.append("order by p.nhso_zone ");

		this.log.info("get summary total from sapbo payable with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryZone.class);
		final List<SummaryZone> results = sql.list();
		session.close();
		this.log.info("end get summary total from sapbo payable........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public synchronized List<SummaryZone> getSumTotalTransfer(
			final Date fromDate, final Date toDate) throws Exception {
		this.log.info("get summary total from sapbo transfer from date : "
				+ fromDate + " to date : " + toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select p.nhso_zone as zoneId, p.nhso_zonename as zoneName, amount ");
		sb.append("from province p left join ( ");
		sb.append("select s.zoneid as zoneId, sum(s.amount) as amount from m_seamless s where s.status='T' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.repno in (select re.ref_doc_no from ods_dw_regup re ");
		sb.append("where re.idct_prop_run<>'X' and re.pmnt_doc_no<>' ' and trunc(re.run_dt)<trunc(sysdate)) ");
		sb.append("group by s.zoneid)");
		sb.append("on p.nhso_zone=zoneid where p.nhso_zone < 14 ");
		sb.append("group by p.nhso_zone, p.nhso_zonename , amount ");
		sb.append("order by p.nhso_zone ");

		this.log.info("get summary total from sapbo transfer with sql query : "
				+ sb.toString());
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryZone.class);
		final List<SummaryZone> results = sql.list();
		session.close();
		this.log.info("end get summary total from sapbo transfer..........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getRepNoByHospitalCodeProcessCheckCompensation(final String hospitalCode, final Date fromDate, final Date toDate,
			final String repNo, final String authType)
			throws Exception {
		this.log.info("get repno step compensation by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date : "
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select s.repno as repNo, s.amount as amount, s.charge_cost as total, s.sums_serviceitem as sumsServiceItem, s.oprefer as orfAmount from m_seamless s ");
		sb.append("where s.status='C' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.hospitalcode='").append(hospitalCode).append("' ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and s.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and s.auth_type='").append(authType).append("' ");
		}
		sb.append("group by s.repno, s.amount, s.charge_cost, s.sums_serviceitem, s.oprefer ");
		sb.append("order by s.repno desc");
		this.log.info("get repno step compensation with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno step compensation.........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getRepNoByHospitalCodeProcessPetition(final String hospitalCode, final Date fromDate, final Date toDate,
			final String repNo, final String authType)
			throws Exception {
		this.log.info("get repno step petition by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date : "
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select s.repno as repNo, s.amount as amount, s.charge_cost as total, s.sums_serviceitem as sumsServiceItem, s.oprefer as orfAmount from m_seamless s ");
		sb.append("where s.status='E' ");
		sb.append("and s.datesend >= to_date('")
				.append(DateFormatUtils.dateToStr(fromDate))
				.append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('")
				.append(DateFormatUtils.dateToStr(toDate))
				.append("', 'dd/MM/yyyy') ");
		sb.append("and s.hospitalcode='").append(hospitalCode).append("' ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and s.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and s.auth_type='").append(authType).append("' ");
		}
		sb.append("group by s.repno, s.amount, s.charge_cost, s.sums_serviceitem, s.oprefer ");
		sb.append("order by s.repno desc");

		this.log.info("get repno step petition with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno step petition.......");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getRepNoByHospitalCodeProcessBudget(final String hospitalCode, final Date fromDate, final Date toDate,
			final String repNo, final String authType)
			throws Exception {
		this.log.info("get repno step budget by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date : "
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select s.repno as repNo, sum(zf.amnt) as amount, s.charge_cost as total, s.sums_serviceitem as sumsServiceItem, s.oprefer as orfAmount  ");
		sb.append("from m_seamless s, ods_dw_zif_ebudget zf  ");
		sb.append("where s.repno=zf.perd and trim(zf.ptnr)=trim(to_char(s.hospitalcode, '0000000000')) "); 
		sb.append("and zf.post_dt=to_date('19000101', 'yyyyMMdd') ");
		sb.append("and s.status='B' ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.hospitalcode='").append(hospitalCode).append("' ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and s.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and s.auth_type='").append(authType).append("' ");
		}
		sb.append("group by s.repno, s.charge_cost, s.sums_serviceitem, s.oprefer ");
		sb.append("order by s.repno desc");

		this.log.info("get repno step budget with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno step budget...........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getRepNoByHospitalCodePayable(final String hospitalCode, final Date fromDate, final Date toDate,
			final String repNo, final String authType)
			throws Exception {
		this.log.info("get repno step payable by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date :"
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		sb.append("select repNo, sum(total) as total, amount, sum(sumsServiceItem) as sumsServiceItem, orfAmount from ( ");
		sb.append("select s.repno as repNo, s.charge_cost as total, sum(zf.amnt) as amount, s.sums_serviceitem as sumsServiceItem, s.oprefer as orfAmount  ");
		sb.append("from m_seamless s, ods_dw_zif_ebudget zf  ");
		sb.append("where s.repno=zf.ref_doc_no and trim(zf.ptnr)=trim(to_char(s.hospitalcode, '0000000000')) ");
		sb.append("and s.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and s.hospitalcode='").append(hospitalCode).append("' and s.status='P' ");
		sb.append("and zf.post_dt<>to_date('19000101', 'yyyyMMdd') ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and s.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and s.auth_type='").append(authType).append("' ");
		}
		sb.append("group by s.repno, s.charge_cost, s.sums_serviceitem, s.oprefer ");
		sb.append("union ");
		sb.append("select sss.repno as repNo, sss.charge_cost as total, sum(r.amnt) as amount, sss.sums_serviceitem as sumsServiceItem, 0 as orfAmount  ");
		sb.append("from m_seamless sss, ods_dw_regup r where sss.repno=r.ref_doc_no ");
		sb.append("and trim(r.vndr_no)=trim(to_char(sss.hospitalcode, '0000000000')) ");
		sb.append("and sss.datesend >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and sss.datesend <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and sss.hospitalcode='").append(hospitalCode).append("' and sss.status='T' ");
		sb.append("and r.idct_prop_run<>'X' and r.pmnt_doc_no<>' ' and sss.repno in (select re.ref_doc_no from ods_dw_regup re ");
		sb.append("where re.vndr_no like '%").append(hospitalCode).append("' and trunc(re.run_dt)>trunc(sysdate) and sss.hospitalcode = '").append(hospitalCode).append("' ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and re.ref_doc_no='").append(repNo).append("' ");
		}
		sb.append(") ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and sss.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and sss.auth_type='").append(authType).append("' ");
		}
		sb.append("group by sss.repno, sss.charge_cost, sss.sums_serviceitem ) ");
		sb.append("group by repNo, amount, orfAmount ");
		sb.append("order by repNo desc");

		this.log.info("get repno step payable with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno step payable............");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackChart> getRepNoByHospitalCodeTransfer(final String hospitalCode, final Date fromDate, final Date toDate,
			final String repNo, final String authType)
			throws Exception {
		this.log.info("get repno step transfer by hospital code : "
				+ hospitalCode + " from date : " + fromDate + " to date : "
				+ toDate);

		final StringBuilder sb = new StringBuilder();
		
		sb.append("select a.repNo, a.total, a.sumsServiceItem, sum(a.amountH) - sum(a.amountS) as amount, orfAmount from  	");
		sb.append("(select s.repno as repNo, s.charge_cost as total, s.sums_serviceitem as sumsServiceItem, ");
		sb.append("sum(case when r.dr_cr_idct is not null and r.dr_cr_idct = 'H' then ");
		sb.append("r.amnt  else 0 end ) as amountH, ");
		sb.append("sum(case when r.dr_cr_idct is not null and r.dr_cr_idct = 'S' then ");
		sb.append("r.amnt  else 0 end ) as amountS, ");
		sb.append("r.dr_cr_idct as expression, sum(s.oprefer) as orfAmount   ");
		sb.append("from m_seamless s, ods_dw_regup r ");
		sb.append("where s.repno=r.ref_doc_no and trim(r.vndr_no)=trim(to_char(s.hospitalcode, '0000000000')) ");
		sb.append("and s.datesend >= to_date('")
				.append(DateFormatUtils.dateToStr(fromDate))
				.append("', 'dd/MM/yyyy') ");
		sb.append("and s.datesend <= to_date('")
				.append(DateFormatUtils.dateToStr(toDate))
				.append("', 'dd/MM/yyyy') ");
		sb.append("and s.hospitalcode='").append(hospitalCode).append("' ");
		sb.append("and s.status='T' ");
		sb.append("and r.idct_prop_run<>'X' and r.pmnt_doc_no<>' ' ");
		sb.append("and s.repno not in (select re.ref_doc_no from ods_dw_regup re where re.vndr_no like '%").append(hospitalCode).append("' ");
		sb.append("and trunc(re.run_dt)>trunc(sysdate)) ");
		if(repNo != null && repNo.length() > 0){
			sb.append("and s.repno='").append(repNo).append("' ");
		}
		if(authType != null && authType.length() > 0){
			sb.append("and s.auth_type='").append(authType).append("' ");
		}
		sb.append("group by s.repno, s.charge_cost, s.sums_serviceitem, r.dr_cr_idct) a ");
		sb.append("group by repNo, total, sumsServiceItem, orfAmount "); 
		sb.append("order by a.repNo desc");

		this.log.info("get repno step transfer with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackChart.class);
		final List<SummaryTrackChart> results = sql.list();
		session.close();
		this.log.info("end get repno step transfer..........");

		return results;
	}

	
	public Integer getCountAllPersonDetailsData(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate, final String searchBy, final String searchValue) throws Exception {
		this.log.info("count all person from details data ");
		final StringBuilder sb = new StringBuilder();
		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename ");
		sb.append("and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		
		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		this.log.info("sql count all person from details data : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final Integer result = (Integer) sql.uniqueResult();
		session.close();
		this.log.info("end count all person from details data...... ");
		
		return result;
	}
	
	public Integer getCountPersonNotPassDenyDetailsData(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate, final String searchBy, final String searchValue) throws Exception {
		this.log.info("count person not pass deny from details data ");
		final StringBuilder sb = new StringBuilder();
		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename ");
		sb.append("and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		sb.append("and m.code_id='DENY' ");

		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		this.log.info("sql person not pass deny from details data : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final Integer result = (Integer) sql.uniqueResult();
		session.close();
		this.log.info("end person not pass deny from details data...... ");
		
		return result;
	}
	
	public Integer getCountPersonNotPassDetailsData(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate, final String searchBy, final String searchValue) throws Exception {
		this.log.info("count person not pass from details data ");
		final StringBuilder sb = new StringBuilder();
		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename ");
		sb.append("and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		sb.append("and m.code_id<>'DENY' and m.code_id is not null ");

		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		this.log.info("sql person not pass from details data : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final Integer result = (Integer) sql.uniqueResult();
		session.close();
		this.log.info("end person not pass from details data...... ");
		
		return result;
	}
	
	public Integer getCountPersonAppealDetailsData(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate, final String searchBy, final String searchValue) throws Exception {
		this.log.info("count person appeal from details data ");
		final StringBuilder sb = new StringBuilder();
		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename ");
		sb.append("and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		sb.append("and m.filename_send like '%APPEAL%' ");

		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		this.log.info("sql person appeal from details data : " + sb.toString());
		
		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final Integer result = (Integer) sql.uniqueResult();
		session.close();
		this.log.info("end person appeal from details data...... ");
		
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackPidData> getDetailsByPID(final String hospitalCode,
			final String type, final String code, final Date dateFromAdmidTime,
			final Date dateToAdmidTime) throws Exception {
		this.log.info("get details data from code : " + code + " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select rownum as id,a.* ");
		sb.append(", to_number(replace(fn_sums_oprefer_hmain(tranId), ',', '')) as orfHMain, ");
		sb.append("to_number(replace(fn_sums_oprefer_nhso(tranId), ',', '')) as orfNHSO ");
		sb.append("from( ");
		sb.append("select ");
		
		sb.append("(select (case when (trim(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) is not null) then(lfa1.nm1||' '||lfa1.nm2||' '||lfa1.nm3||' '||lfa1.nm4) else (wel2.h_nm) end)");
		sb.append("from ODS_DW_LFA1 lfa1, ODS_DW_ZHOSPITAL_WEL2 wel2 where lfa1.vndr_no = wel2.h_cd and nhso_zone_id not in ('14', '15', '99') ");
		sb.append("and wel2.used_data = 'Y' AND SUBSTR (TRIM (lfa1.vndr_no), 1, 1) NOT IN ('E', 'A', 'P') and trim(lfa1.vndr_no)=trim(to_char(s.hospitalcode, '0000000000'))) as hospitalName, ");
		sb.append("s.statusType, s.hospitalCode, s.repNo, s.pid, s.hn, s.an, s.fullname");
		sb.append(",s.admidDate, s.dischargeDate, s.admidTime, s.dischargeTime, s.discharge, s.personType, s.compensation, s.salaryPercent, s.salaryAmount");
		sb.append(",s.chargeRule, s.orf, s.status, s.sumsHC, s.sumsAE, s.sumsINST, s.sumsIP, s.sumsDMIS, s.fund, s.parentTranId");
		sb.append(",s.codeId, s.hc, ae, s.inst, s.ip, s.dmis, s.oldTranId, s.oldfund, s.oldAmount, s.oldTotal");
		sb.append(",s.codeop, s.codepa, s.codeot, s.sumsOP, s.sumsPA, s.sumsOT, s.oldSumsHC, s.oldSumsAE, s.oldSumsINST, s.oldSumsIP, s.oldSumsDMIS");
		sb.append(",s.oldSumsOP, s.oldSumsPA, s.oldSumsOT,s.oldCodeId, s.oldHC, s.oldAE, s.oldINST");
		sb.append(",s.oldIP,s.oldDMIS,s.oldOP,s.oldPA,s.oldOT, s.oldSumsDRUG, s.sumsDRUG ");
		sb.append(",dw.run_dt as paymentDate, dw.adtl_id as batchId, dw.dr_cr_idct as expression, s.tranId, s.eclaimNo, s.authType ");

		sb.append("from ");
		sb.append("(");
		sb.append("select ");		
		sb.append("rownum as id, statusType, hospitalCode, hospitalName, repNo, inpid as pid, inhn as hn, inan as an, fullname");
		sb.append(",admidDate, dischargeDate, admidTime, dischargeTime, discharge, personType,compensation, salaryPercent, salaryAmount");
		sb.append(",chargeRule, orf, instatus as status, sumsHC, sumsAE, sumsINST, sumsIP, sumsDMIS, fund/*,batchId, paymentDate*/, parentTranId ");
		sb.append(",codeId, hc, ae, inst, ip, dmis, sr.TRAN_ID as oldTranId, sr.FUND_AF as oldfund, sr.SUMS_COST as oldAmount, sr.CHARGE_COST as oldTotal");
		sb.append(",codeop, codepa, codeot, sumsOP, sumsPA, sumsOT, sumsDRUG, eclaimNo, tranId, authType, ");
		sb.append("case when sr.deny_hc = 'C' and sr.code_id is null then case when  (");
		sb.append("case when sr.sums_iphc is not null and sr.filename_send not like '%ORF%' then sr.sums_iphc  else 0 end + ");
		sb.append("case when sr.sums_ophc_01 is not null then sr.sums_ophc_01 else 0 end + ");
		sb.append("case when sr.sums_ophc_02 is not null then sr.sums_ophc_02 else 0 end + ");
		sb.append("case when sr.sums_ophc_03 is not null then sr.sums_ophc_03 else 0 end + ");
		sb.append("case when sr.sums_ophc_04 is not null then sr.sums_ophc_04 else 0 end + ");
		sb.append("case when sr.sums_ophc_05 is not null then sr.sums_ophc_05 else 0 end + ");
		sb.append("case when sr.sums_ophc_06 is not null then sr.sums_ophc_06 else 0 end + ");
		sb.append("case when sr.sums_ophc_07 is not null then sr.sums_ophc_07 else 0 end + ");
		sb.append("case when sr.sums_ophc_08 is not null then sr.sums_ophc_08 else 0 end ) <> 0 ");
		sb.append(" then ");
		sb.append("case when sr.sums_iphc is not null and sr.filename_send not like '%ORF%' then sr.sums_iphc  else 0 end + ");
		sb.append("case when sr.sums_ophc_01 is not null then sr.sums_ophc_01 else 0 end + ");
		sb.append("case when sr.sums_ophc_02 is not null then sr.sums_ophc_02 else 0 end + ");
		sb.append("case when sr.sums_ophc_03 is not null then sr.sums_ophc_03 else 0 end + ");
		sb.append("case when sr.sums_ophc_04 is not null then sr.sums_ophc_04 else 0 end + ");
		sb.append("case when sr.sums_ophc_05 is not null then sr.sums_ophc_05 else 0 end + ");
		sb.append("case when sr.sums_ophc_06 is not null then sr.sums_ophc_06 else 0 end + ");
		sb.append("case when sr.sums_ophc_07 is not null then sr.sums_ophc_07 else 0 end + ");
		sb.append("case when sr.sums_ophc_08 is not null then sr.sums_ophc_08 else 0 end ");
		sb.append("when sr.sums_ophc is not null then sr.sums_ophc end ");
		sb.append("else 0 ");
		sb.append("end as oldSumsHC, ");

		sb.append("case when sr.deny_ae = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_opae is not null then sr.sums_opae   else 0 end + ");
		sb.append("case when sr.sums_ipnb is not null then sr.sums_ipnb   else 0 end + ");
		sb.append("case when sr.sums_ippuc is not null then sr.sums_ippuc   else 0 end + ");
		sb.append("case when sr.sums_ip3sss is not null then sr.sums_ip3sss   else 0 end + ");
		sb.append("case when sr.sums_ip7sss is not null then sr.sums_ip7sss   else 0 end + ");
		sb.append("case when sr.sums_carae is not null then sr.sums_carae   else 0 end + ");
		sb.append("case when sr.sums_caref is not null then sr.sums_caref   else 0 end + ");
		sb.append("case when sr.sums_caref_puc is not null then sr.sums_caref_puc   else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as oldSumsAE, ");

		sb.append("case when sr.deny_inst = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_opinst is not null then sr.sums_opinst else 0 end + ");
		sb.append("case when sr.sums_inst is not null then sr.sums_inst else 0 end ) ");
		sb.append("end as oldSumsINST, ");


		sb.append("case when sr.deny_ip = 'C' and sr.code_id is null  then case when ( ");
		sb.append("case when sr.sums_ipaec is not null then sr.sums_ipaec   else 0 end +  ");
		sb.append("case when sr.sums_ipaer is not null then sr.sums_ipaer  else 0 end + ");
		sb.append("case when sr.sums_ipinrgc is not null then sr.sums_ipinrgc  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgr is not null then sr.sums_ipinrgr  else 0 end + "); 
		sb.append("case when sr.sums_ipinspsn is not null then sr.sums_ipinspsn  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc is not null then sr.sums_ipprcc  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc_puc is not null then sr.sums_ipprcc_puc  else 0 end) <> 0 "); 
		sb.append("then "); 
		sb.append("case when sr.sums_ipaec is not null then sr.sums_ipaec   else 0 end + ");  
		sb.append("case when sr.sums_ipaer is not null then sr.sums_ipaer  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgc is not null then sr.sums_ipinrgc  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgr is not null then sr.sums_ipinrgr  else 0 end + "); 
		sb.append("case when sr.sums_ipinspsn is not null then sr.sums_ipinspsn  else 0 end +  ");
		sb.append("case when sr.sums_ipprcc is not null then sr.sums_ipprcc  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc_puc is not null then sr.sums_ipprcc_puc  else 0 end "); 
		sb.append("when sr.sums_ip is not null then ");
		sb.append("sr.sums_ip end ");
		sb.append("else 0 "); 	
		sb.append("end as oldSumsIP, ");

		sb.append("case when sr.deny_dmis = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_cataract is not null then sr.sums_cataract  else 0 end +  ");
		sb.append("          case         when sr.EXPENSE_AMT_PROV is not null then sr.EXPENSE_AMT_PROV  else 0 end +  ");
		sb.append("           case        when sr.EXPENSE_AMT_HOSP is not null then sr.EXPENSE_AMT_HOSP   else 0 end +  ");
		sb.append("case when sr.sums_catinst is not null then sr.sums_catinst  else 0 end + ");
		sb.append("case when sr.sums_dmisrc is not null then sr.sums_dmisrc  else 0 end + ");
		sb.append("case when sr.sums_rcuhosc is not null then sr.sums_rcuhosc  else 0 end + ");
		sb.append("case when sr.sums_rcuhosr is not null then sr.sums_rcuhosr  else 0 end + ");
		sb.append("case when sr.sums_llop is not null then sr.sums_llop  else 0 end + ");
		sb.append("case when sr.sums_llrgc is not null then sr.sums_llrgc  else 0 end + ");
		sb.append("case when sr.sums_llrgr is not null then sr.sums_llrgr  else 0 end + ");
		sb.append("case when sr.sums_lp is not null then sr.sums_lp  else 0 end + ");
		sb.append("case when sr.sums_stroke_drug is not null then sr.sums_stroke_drug  else 0 end + ");
		sb.append("case when sr.sums_dmidml is not null then sr.sums_dmidml  else 0 end + ");
		sb.append("case when sr.sums_fp is not null then sr.sums_fp  else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as oldSumsDMIS, ");

		sb.append("case when sr.deny_op = 'C' and sr.code_id is null then ");
		sb.append("case when sr.sums_op is not null then sr.sums_op ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as oldSumsOP, ");

		sb.append("case when sr.deny_pa = 'C' and sr.code_id is null then ");
		sb.append("case when sr.sums_pa is not null then sr.sums_pa ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as oldSumsPA, ");

		sb.append("case when sr.deny_ot = 'C' and sr.code_id is null then ");
		sb.append("case when sr.sums_ot is not null then sr.sums_ot ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as oldSumsOT, ");
		
		sb.append("      case when sr.sums_drug is not null and sr.code_id is null");
		sb.append("            then sr.sums_drug ");
		sb.append("			else 0 ");
		sb.append("            end as oldSumsDRUG, ");

		sb.append("sr.code_id as oldCodeId, sr.deny_hc as oldHC, sr.deny_ae as oldAE, sr.deny_inst as oldINST,  sr.deny_ip as oldIP ");
		sb.append(", sr.deny_dmis as oldDMIS, sr.DENY_OP as oldOP, sr.DENY_PA as oldPA, sr.DENY_OT as oldOT ");

		sb.append("from ( ");
		sb.append("select t.PID as inpid,m.status as statusType,t.hcode as hospitalCode,t.hname as hospitalName,t.rep as repNo ");
		sb.append(", t.hn as inhn, t.an as inan, t.NAME as fullname, to_date(t.DATEADM, 'yyyyMMdd') as admidDate ");
		sb.append(", to_date(t.DATEDSC, 'yyyyMMdd') as dischargeDate, t.timeadm as admidTime, ");
		sb.append("case when t.person_type is not null and t.person_type_name is not null then t.person_type_name ");
		sb.append("else ");
		sb.append("case when (t.hn is not null and t.hn<>'-') and (t.an is null or t.an<>'-') then 'OP' ");
		sb.append("when (t.hn is null or t.hn<>'-') and (t.an is not null and t.an<>'-') then 'IP' ");
		sb.append("when (t.hn is not null and t.hn<>'-') and (t.an is not null and t.an<>'-') then 'IP' end ");
		sb.append("end as personType, ");
		sb.append("t.timedsc as dischargeTime, t.CHARGE_COST as discharge ");
		sb.append(", t.SUMS_COST as compensation, t.rate as salaryPercent, t.SUMS_SALARY as salaryAmount, t.charge_rule as chargeRule ");
		sb.append(", case when t.filename_send like '%ORF%' then 'Y' end as orf");
		sb.append(", case when t.filename_send like '%APPEAL%' then 'A' end as instatus, ");
		sb.append("case when t.deny_hc = 'C' and t.code_id is null then case when  (");
		sb.append("case when t.sums_iphc is not null and t.filename_send not like '%ORF%' then t.sums_iphc  else 0 end + ");
		sb.append("case when t.sums_ophc_01 is not null then t.sums_ophc_01 else 0 end + ");
		sb.append("case when t.sums_ophc_02 is not null then t.sums_ophc_02 else 0 end + ");
		sb.append("case when t.sums_ophc_03 is not null then t.sums_ophc_03 else 0 end + ");
		sb.append("case when t.sums_ophc_04 is not null then t.sums_ophc_04 else 0 end + ");
		sb.append("case when t.sums_ophc_05 is not null then t.sums_ophc_05 else 0 end + ");
		sb.append("case when t.sums_ophc_06 is not null then t.sums_ophc_06 else 0 end + ");
		sb.append("case when t.sums_ophc_07 is not null then t.sums_ophc_07 else 0 end + ");
		sb.append("case when t.sums_ophc_08 is not null then t.sums_ophc_08 else 0 end ) <> 0 ");
		sb.append(" then ");
		sb.append("case when t.sums_iphc is not null and t.filename_send not like '%ORF%' then t.sums_iphc  else 0 end + ");
		sb.append("case when t.sums_ophc_01 is not null then t.sums_ophc_01 else 0 end + ");
		sb.append("case when t.sums_ophc_02 is not null then t.sums_ophc_02 else 0 end + ");
		sb.append("case when t.sums_ophc_03 is not null then t.sums_ophc_03 else 0 end + ");
		sb.append("case when t.sums_ophc_04 is not null then t.sums_ophc_04 else 0 end + ");
		sb.append("case when t.sums_ophc_05 is not null then t.sums_ophc_05 else 0 end + ");
		sb.append("case when t.sums_ophc_06 is not null then t.sums_ophc_06 else 0 end + ");
		sb.append("case when t.sums_ophc_07 is not null then t.sums_ophc_07 else 0 end + ");
		sb.append("case when t.sums_ophc_08 is not null then t.sums_ophc_08 else 0 end ");
		sb.append("when t.sums_ophc is not null then t.sums_ophc end ");
		sb.append("else 0 ");
		sb.append("end as sumsHC, ");

		sb.append("case when t.deny_ae = 'C' and t.code_id is null then (");
		sb.append("case when t.sums_opae is not null then t.sums_opae   else 0 end + ");
		sb.append("case when t.sums_ipnb is not null then t.sums_ipnb   else 0 end + ");
		sb.append("case when t.sums_ippuc is not null then t.sums_ippuc   else 0 end + ");
		sb.append("case when t.sums_ip3sss is not null then t.sums_ip3sss   else 0 end + ");
		sb.append("case when t.sums_ip7sss is not null then t.sums_ip7sss   else 0 end + ");
		sb.append("case when t.sums_carae is not null then t.sums_carae   else 0 end + ");
		sb.append("case when t.sums_caref is not null then t.sums_caref   else 0 end + ");
		sb.append("case when t.sums_caref_puc is not null then t.sums_caref_puc   else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as sumsAE, ");

		sb.append("case when t.deny_inst = 'C' and t.code_id is null then (");
		sb.append("case when t.sums_opinst is not null then t.sums_opinst else 0 end + ");
		sb.append("case when t.sums_inst is not null then t.sums_inst else 0 end ) ");
		sb.append("end as sumsINST, ");

		sb.append("case when t.deny_ip = 'C' and t.code_id is null  then case when ( ");
		sb.append("case when t.sums_ipaec is not null then t.sums_ipaec   else 0 end +  ");
		sb.append("case when t.sums_ipaer is not null then t.sums_ipaer  else 0 end + ");
		sb.append("case when t.sums_ipinrgc is not null then t.sums_ipinrgc  else 0 end + "); 
		sb.append("case when t.sums_ipinrgr is not null then t.sums_ipinrgr  else 0 end + "); 
		sb.append("case when t.sums_ipinspsn is not null then t.sums_ipinspsn  else 0 end + "); 
		sb.append("case when t.sums_ipprcc is not null then t.sums_ipprcc  else 0 end + "); 
		sb.append("case when t.sums_ipprcc_puc is not null then t.sums_ipprcc_puc  else 0 end) <> 0 "); 
		sb.append("then "); 
		sb.append("case when t.sums_ipaec is not null then t.sums_ipaec   else 0 end + ");  
		sb.append("case when t.sums_ipaer is not null then t.sums_ipaer  else 0 end + "); 
		sb.append("case when t.sums_ipinrgc is not null then t.sums_ipinrgc  else 0 end + "); 
		sb.append("case when t.sums_ipinrgr is not null then t.sums_ipinrgr  else 0 end + "); 
		sb.append("case when t.sums_ipinspsn is not null then t.sums_ipinspsn  else 0 end +  ");
		sb.append("case when t.sums_ipprcc is not null then t.sums_ipprcc  else 0 end + "); 
		sb.append("case when t.sums_ipprcc_puc is not null then t.sums_ipprcc_puc  else 0 end "); 
		sb.append("when t.sums_ip is not null then ");
		sb.append("t.sums_ip end ");
		sb.append("else 0 "); 	
		sb.append("end as sumsIP, ");

		sb.append("case when t.deny_dmis = 'C' and t.code_id is null then (");
		sb.append("case when t.sums_cataract is not null then t.sums_cataract  else 0 end +  ");
		sb.append("          case         when t.EXPENSE_AMT_PROV is not null then t.EXPENSE_AMT_PROV  else 0 end +  ");
		sb.append("           case        when t.EXPENSE_AMT_HOSP is not null then t.EXPENSE_AMT_HOSP   else 0 end +  ");
		sb.append("case when t.sums_catinst is not null then t.sums_catinst  else 0 end + ");
		sb.append("case when t.sums_dmisrc is not null then t.sums_dmisrc  else 0 end + ");
		sb.append("case when t.sums_rcuhosc is not null then t.sums_rcuhosc  else 0 end + ");
		sb.append("case when t.sums_rcuhosr is not null then t.sums_rcuhosr  else 0 end + ");
		sb.append("case when t.sums_llop is not null then t.sums_llop  else 0 end + ");
		sb.append("case when t.sums_llrgc is not null then t.sums_llrgc  else 0 end + ");
		sb.append("case when t.sums_llrgr is not null then t.sums_llrgr  else 0 end + ");
		sb.append("case when t.sums_lp is not null then t.sums_lp  else 0 end + ");
		sb.append("case when t.sums_stroke_drug is not null then t.sums_stroke_drug  else 0 end + ");
		sb.append("case when t.sums_dmidml is not null then t.sums_dmidml  else 0 end + ");
		sb.append("case when t.sums_fp is not null then t.sums_fp  else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as sumsDMIS, ");

		sb.append("case when t.deny_op = 'C' and t.code_id is null then ");
		sb.append("case when t.sums_op is not null then t.sums_op ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as sumsOP, ");

		sb.append("case when t.deny_pa = 'C' and t.code_id is null then ");
		sb.append("case when t.sums_pa is not null then t.sums_pa ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as sumsPA, ");

		sb.append("case when t.deny_ot = 'C' and t.code_id is null then ");
		sb.append("case when t.sums_ot is not null then t.sums_ot ");
		sb.append("else 0 ");
		sb.append("end ");
		sb.append("end as sumsOT, ");
		
		sb.append("      case when t.sums_drug is not null and t.code_id is null");
		sb.append("            then t.sums_drug ");
		sb.append("			else 0 ");
		sb.append("            end as sumsDRUG, ");

		sb.append("t.fund_af as fund, t.parent_tran_id as parentTranId, t.code_id as codeId, t.deny_hc as hc ");
		sb.append(", t.deny_ae as ae, t.deny_inst as inst, t.deny_ip as ip, t.deny_dmis as dmis, t.DENY_OP as codeop ");
		sb.append(", t.DENY_PA as codepa, t.DENY_OT as codeot, t.tran_id as tranId, t.eclaim_no as eclaimNo, ");
		
		sb.append("case when t.filename_send like '%LG%' then 'LGO' ");
		sb.append(" when t.filename_send not like '%LG%' and ");
		sb.append(" t.filename_send not like '%SS%' and ");
		sb.append(" t.filename_send not like '%CS%' then 'UC' ");
		sb.append(" else '' end as authType ");
		
		sb.append("from M_SEAMLESS_REP t, M_REGISTER h,M_SEAMLESS m ");
		sb.append("where h.TRAN_ID = t.TRAN_ID and m.repno = t.rep and m.hospitalcode = t.hcode ");
		  
		sb.append("and ").append("t." + type).append("='").append(code)
				.append("' ");

		if (hospitalCode != null && hospitalCode.length() > 0) {
			sb.append("and t.HCODE='").append(hospitalCode).append("' ");
		}

		if (dateFromAdmidTime != null) {
			sb.append("and to_date(t.DATEADM, 'yyyyMMdd') >= to_date('")
					.append(DateFormatUtils.dateToStr(dateFromAdmidTime))
					.append("', 'dd/mm/yyyy') ");
		}

		if (dateToAdmidTime != null) {
			sb.append("and to_date(t.DATEADM, 'yyyyMMdd') <= to_date('")
					.append(DateFormatUtils.dateToStr(dateToAdmidTime))
					.append("', 'dd/mm/yyyy') ");
		}
		sb.append("and h.FILENAME_SEND not like '%CS%' and h.FILENAME_SEND not like '%OFC%' "); 
		sb.append("and h.FILENAME_SEND not like '%SS%' ");
		
		sb.append(") left join m_seamless_rep sr on parentTranId=sr.TRAN_ID and tran_id = sr.tran_id ");
		sb.append(") s left join ods_dw_regup dw on dw.ref_doc_no=s.repNo and substr(dw.vndr_no, 6)= s.hospitalCode and  dw.idct_prop_run<>'X' and dw.pmnt_doc_no<>' ' ");

		sb.append("group by ");
		sb.append("s.statusType, s.hospitalCode, s.hospitalName, s.repNo, s.pid, s.hn, s.an, s.fullname ");
		sb.append(",s.admidDate, s.dischargeDate, s.admidTime, s.dischargeTime, s.discharge, s.personType, s.compensation, s.salaryPercent, s.salaryAmount ");
		sb.append(",s.chargeRule, s.status, s.sumsHC, s.sumsAE, s.sumsINST, s.sumsIP, s.sumsDMIS, s.fund, s.parentTranId ");
		sb.append(",s.codeId, s.hc, ae, s.inst, s.ip, s.dmis, s.oldTranId, s.oldfund, s.oldAmount, s.oldTotal ");
		sb.append(",s.codeop, s.codepa, s.codeot, s.sumsOP, s.sumsPA, s.sumsOT, s.oldSumsHC, s.oldSumsAE, s.oldSumsINST, s.oldSumsIP, s.oldSumsDMIS ");
		sb.append(",s.oldSumsOP, s.oldSumsPA, s.oldSumsOT,s.oldCodeId, s.oldHC, s.oldAE, s.oldINST ");
		sb.append(",s.oldIP,s.oldDMIS,s.oldOP,s.oldPA,s.oldOT ");
		sb.append(",dw.run_dt, dw.adtl_id, dw.dr_cr_idct, s.oldSumsDRUG, s.sumsDRUG, s.orf, s.tranId, s.eclaimNo, s.authType  ");
		sb.append(") a ");
		sb.append("order by a.admidDate desc ");

		this.log.info("get details data from repno with sql query : "+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackPidData.class);
		final List<SummaryTrackPidData> results = sql.list();
		session.close();
		this.log.info("end get details data from repno..........");

		return results;
	}

	public SummaryTrackAppeal getDataByTranId(final String tranId)
			throws Exception {
		this.log.info("get data by tran id : " + tranId);

		final StringBuilder sb = new StringBuilder();

		sb.append("select s.tran_id as tranId, s.charge_cost as total, s.sums_cost as amount, s.fund_af as fund ");
		sb.append("from m_seamless_rep s ");
		sb.append("where s.tran_id='").append(tranId).append("' ");
		this.log.info("get data by tran id with sql : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackAppeal.class);
		final SummaryTrackAppeal results = (SummaryTrackAppeal) sql
				.uniqueResult();
		session.close();
		this.log.info("end get data by tran id........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackByStatus> getBatchTransferFromRegup(
			final String hospitalCode, final String repNo) throws Exception {
		this.log.info("get data regup : " + hospitalCode);

		final StringBuilder sb = new StringBuilder();
		sb.append("select rownum as id, runDate, batchId, expression, amount ");
		sb.append("from (select r.run_dt as runDate, r.adtl_id as batchId, r.dr_cr_idct as expression, sum(r.amnt) as amount ");
		sb.append("from ods_dw_regup r ");
		sb.append("where r.ref_doc_no='").append(repNo).append("' ");
		sb.append("and r.vndr_no like '%").append(hospitalCode).append("' ");
		sb.append("and r.idct_prop_run<>'X' and r.pmnt_doc_no<>' ' ");
		sb.append("group by r.run_dt, r.adtl_id, r.dr_cr_idct) ");
		sb.append("order by runDate ");
		this.log.info("get data regup with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackByStatus.class);
		final List<SummaryTrackByStatus> results = sql.list();
		session.close();
		this.log.info("end get data regup..........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackByStatus> getBatchTransferFromZIFEbudgetPostDateNull(
			final String hospitalCode, final String repNo) throws Exception {

		final StringBuilder sb = new StringBuilder();
		sb.append("select rownum as id, batchId, runDate, expression, amount from ");
		sb.append("(select ze.btch_no as batchId, to_date(null) as runDate, '' as expression, sum(ze.amnt) as amount ");
		sb.append("from ods_dw_zif_ebudget ze ");
		sb.append("where ze.perd='").append(repNo).append("' and ze.ptnr like '%").append(hospitalCode).append("' ");
		sb.append("and ze.post_dt=to_date('19000101', 'yyyyMMdd') ");
		sb.append("group by ze.btch_no)");

		this.log.info("get data zif e-budget with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackByStatus.class);
		final List<SummaryTrackByStatus> results = sql.list();
		session.close();
		System.err.println(results.toString());
		this.log.info("end get data  zif e-budget..........");

		return results;
	}

	@SuppressWarnings("unchecked")
	public List<SummaryTrackByStatus> getBatchTransferFromZIFEbudgetPostDateNotNull(
			final String hospitalCode, final String repNo) throws Exception {

		final StringBuilder sb = new StringBuilder();
		sb.append("select rownum as id, batchId, runDate, expression, amount from ");
		sb.append("(select ze.btch_no as batchId, to_date(null) as runDate, '' as expression, sum(ze.amnt) as amount ");
		sb.append("from ods_dw_zif_ebudget ze ");
		sb.append("where ze.ref_doc_no='").append(repNo).append("' and ze.ptnr like '%").append(hospitalCode).append("' ");
		sb.append("and ze.post_dt<>to_date('19000101', 'yyyyMMdd') ");
		sb.append("group by ze.btch_no)");
		this.log.info("get data  zif e-budget with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackByStatus.class);
		final List<SummaryTrackByStatus> results = sql.list();
		session.close();
		this.log.info("end get data  zif e-budget..........");

		return results;
	}
	

	@SuppressWarnings("unchecked")
	public List<SummaryTrackDetailsData> getDetailsByRepNo(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate)throws Exception {
		this.log.info("get details data from repno : " + repNo
				+ " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select rownum as id, inpid as pid, inhn as hn, inan as an, fullname, admidDate, dischargeDate, admidTime, dischargeTime, discharge, personType,");
		sb.append("compensation, salaryPercent, salaryAmount, chargeRule, orf, instatus as status, sumsHC, sumsAE, sumsINST, sumsIP, sumsDMIS, fund, ");
		sb.append("parentTranId, codeId, hc, ae, inst, ip, dmis, sr.TRAN_ID as oldTranId, sr.FUND_AF as oldfund, sr.SUMS_COST as oldAmount, sr.CHARGE_COST as oldTotal, ");
		sb.append(" codeop, codepa, codeot, sumsOP, sumsPA, sumsOT, sumsDRUG, eclaimNo, tranId, cntORF, "); 
		sb.append("0 as cntAllpid, 0 as sumsTotal, 0 as cntNotPass, 0 as cntAppeal, 0 as cntNotPassC, ");
	    sb.append("0 as cntPass, 0 as actAmount, "); 
	    sb.append("to_number(replace(fn_sums_oprefer_hmain(tranId), ',', '')) as orfHMain, ");
	    sb.append("to_number(replace(fn_sums_oprefer_nhso(tranId), ',', '')) as orfNHSO, ");
		
		sb.append("case when sr.deny_hc = 'C' and sr.code_id is null then case when  (");
		sb.append("case when sr.sums_iphc is not null and sr.filename_send not like '%ORF%' then sr.sums_iphc  else 0 end + ");
		sb.append("case when sr.sums_ophc_01 is not null then sr.sums_ophc_01 else 0 end + ");
		sb.append("case when sr.sums_ophc_02 is not null then sr.sums_ophc_02 else 0 end + ");
		sb.append("case when sr.sums_ophc_03 is not null then sr.sums_ophc_03 else 0 end + ");
		sb.append("case when sr.sums_ophc_04 is not null then sr.sums_ophc_04 else 0 end + ");
		sb.append("case when sr.sums_ophc_05 is not null then sr.sums_ophc_05 else 0 end + ");
		sb.append("case when sr.sums_ophc_06 is not null then sr.sums_ophc_06 else 0 end + ");
		sb.append("case when sr.sums_ophc_07 is not null then sr.sums_ophc_07 else 0 end + ");
		sb.append("case when sr.sums_ophc_08 is not null then sr.sums_ophc_08 else 0 end ) <> 0 ");
		sb.append(" then ");
		sb.append("case when sr.sums_iphc is not null and sr.filename_send not like '%ORF%' then sr.sums_iphc  else 0 end + ");
		sb.append("case when sr.sums_ophc_01 is not null then sr.sums_ophc_01 else 0 end + ");
		sb.append("case when sr.sums_ophc_02 is not null then sr.sums_ophc_02 else 0 end + ");
		sb.append("case when sr.sums_ophc_03 is not null then sr.sums_ophc_03 else 0 end + ");
		sb.append("case when sr.sums_ophc_04 is not null then sr.sums_ophc_04 else 0 end + ");
		sb.append("case when sr.sums_ophc_05 is not null then sr.sums_ophc_05 else 0 end + ");
		sb.append("case when sr.sums_ophc_06 is not null then sr.sums_ophc_06 else 0 end + ");
		sb.append("case when sr.sums_ophc_07 is not null then sr.sums_ophc_07 else 0 end + ");
		sb.append("case when sr.sums_ophc_08 is not null then sr.sums_ophc_08 else 0 end ");
		sb.append("when sr.sums_ophc is not null then sr.sums_ophc end ");
		sb.append("else 0 ");
		sb.append("end as oldSumsHC, ");

		sb.append("case when sr.deny_ae = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_opae is not null then sr.sums_opae   else 0 end + ");
		sb.append("case when sr.sums_ipnb is not null then sr.sums_ipnb   else 0 end + ");
		sb.append("case when sr.sums_ippuc is not null then sr.sums_ippuc   else 0 end + ");
		sb.append("case when sr.sums_ip3sss is not null then sr.sums_ip3sss   else 0 end + ");
		sb.append("case when sr.sums_ip7sss is not null then sr.sums_ip7sss   else 0 end + ");
		sb.append("case when sr.sums_carae is not null then sr.sums_carae   else 0 end + ");
		sb.append("case when sr.sums_caref is not null then sr.sums_caref   else 0 end + ");
		sb.append("case when sr.sums_caref_puc is not null then sr.sums_caref_puc   else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as oldSumsAE, ");

		sb.append("case when sr.deny_inst = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_opinst is not null then sr.sums_opinst else 0 end + ");
		sb.append("case when sr.sums_inst is not null then sr.sums_inst else 0 end ) ");
		sb.append("end as oldSumsINST, ");

		sb.append("case when sr.deny_ip = 'C' and sr.code_id is null  then case when ( ");
		sb.append("case when sr.sums_ipaec is not null then sr.sums_ipaec   else 0 end +  ");
		sb.append("case when sr.sums_ipaer is not null then sr.sums_ipaer  else 0 end + ");
		sb.append("case when sr.sums_ipinrgc is not null then sr.sums_ipinrgc  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgr is not null then sr.sums_ipinrgr  else 0 end + "); 
		sb.append("case when sr.sums_ipinspsn is not null then sr.sums_ipinspsn  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc is not null then sr.sums_ipprcc  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc_puc is not null then sr.sums_ipprcc_puc  else 0 end) <> 0 "); 
		sb.append("then "); 
		sb.append("case when sr.sums_ipaec is not null then sr.sums_ipaec   else 0 end + ");  
		sb.append("case when sr.sums_ipaer is not null then sr.sums_ipaer  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgc is not null then sr.sums_ipinrgc  else 0 end + "); 
		sb.append("case when sr.sums_ipinrgr is not null then sr.sums_ipinrgr  else 0 end + "); 
		sb.append("case when sr.sums_ipinspsn is not null then sr.sums_ipinspsn  else 0 end +  ");
		sb.append("case when sr.sums_ipprcc is not null then sr.sums_ipprcc  else 0 end + "); 
		sb.append("case when sr.sums_ipprcc_puc is not null then sr.sums_ipprcc_puc  else 0 end "); 
		sb.append("when sr.sums_ip is not null then ");
		sb.append("sr.sums_ip end ");
		sb.append("else 0 "); 	
		sb.append("end as oldSumsIP, ");

		sb.append("case when sr.deny_dmis = 'C' and sr.code_id is null then (");
		sb.append("case when sr.sums_cataract is not null then sr.sums_cataract  else 0 end +  ");
		sb.append("          case         when sr.EXPENSE_AMT_PROV is not null then sr.EXPENSE_AMT_PROV  else 0 end +  ");
		sb.append("           case        when sr.EXPENSE_AMT_HOSP is not null then sr.EXPENSE_AMT_HOSP   else 0 end +  ");
		sb.append("case when sr.sums_catinst is not null then sr.sums_catinst  else 0 end + ");
		sb.append("case when sr.sums_dmisrc is not null then sr.sums_dmisrc  else 0 end + ");
		sb.append("case when sr.sums_rcuhosc is not null then sr.sums_rcuhosc  else 0 end + ");
		sb.append("case when sr.sums_rcuhosr is not null then sr.sums_rcuhosr  else 0 end + ");
		sb.append("case when sr.sums_llop is not null then sr.sums_llop  else 0 end + ");
		sb.append("case when sr.sums_llrgc is not null then sr.sums_llrgc  else 0 end + ");
		sb.append("case when sr.sums_llrgr is not null then sr.sums_llrgr  else 0 end + ");
		sb.append("case when sr.sums_lp is not null then sr.sums_lp  else 0 end + ");
		sb.append("case when sr.sums_stroke_drug is not null then sr.sums_stroke_drug  else 0 end + ");
		sb.append("case when sr.sums_dmidml is not null then sr.sums_dmidml  else 0 end + ");
		sb.append("case when sr.sums_fp is not null then sr.sums_fp  else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as oldSumsDMIS, ");
		sb.append("    case when sr.deny_op = 'C' and sr.code_id is null then");
		sb.append("     case when sr.sums_op is not null then sr.sums_op");
		sb.append("			else 0 ");
		sb.append("             end end as oldSumsOP,  ");
		sb.append("      case when sr.deny_pa = 'C' and sr.code_id is null then");
		sb.append("           case when sr.sums_pa is not null then sr.sums_pa");
		sb.append("			else 0 ");
		sb.append("             end end as oldSumsPA,  ");
		sb.append("      case when sr.deny_ot = 'C' and sr.code_id is null then");
		sb.append("           case when sr.sums_ot is not null then sr.sums_ot");
		sb.append("			else 0 ");
		sb.append("             end end as oldSumsOT, ");
		sb.append("      case when sr.sums_drug is not null and sr.code_id is null");
		sb.append("            then sr.sums_drug ");
		sb.append("			else 0 ");
		sb.append("            end as oldSumsDRUG, ");
		sb.append("sr.code_id as oldCodeId, sr.deny_hc as oldHC, sr.deny_ae as oldAE, sr.deny_inst as oldINST,  ");
		sb.append("sr.deny_ip as oldIP, sr.deny_dmis as oldDMIS, sr.DENY_OP as oldOP, sr.DENY_PA as oldPA, sr.DENY_OT as oldOT   ");

		sb.append("from (select t.PID as inpid, t.hn as inhn, t.an as inan, t.NAME as fullname, to_date(t.DATEADM, 'yyyyMMdd') as admidDate, ");
		sb.append("case when t.person_type is not null and t.person_type_name is not null then t.person_type_name ");
		sb.append("else ");
		sb.append("case when (t.hn is not null and t.hn<>'-') and (t.an is null or t.an<>'-') then 'OP' ");
		sb.append("when (t.hn is null or t.hn<>'-') and (t.an is not null and t.an<>'-') then 'IP' ");
		sb.append("when (t.hn is not null and t.hn<>'-') and (t.an is not null and t.an<>'-') then 'IP' end ");
		sb.append("end as personType, ");
		sb.append("to_date(t.DATEDSC, 'yyyyMMdd') as dischargeDate, t.timeadm as admidTime, t.timedsc as dischargeTime, t.CHARGE_COST as discharge, ");
		sb.append("t.SUMS_COST as compensation, t.rate as salaryPercent, t.SUMS_SALARY as salaryAmount, t.charge_rule as chargeRule, ");
		sb.append("case when t.filename_send like '%ORF%' then 'Y' end as orf, ");
		sb.append("case when t.filename_send like '%APPEAL%' then 'A' end as instatus, ");
		sb.append("case when t.deny_hc = 'C' and t.code_id is null then case when  (");
		sb.append("case when t.sums_iphc is not null and t.filename_send not like '%ORF%' then t.sums_iphc  else 0 end + ");
		sb.append("case when t.sums_ophc_01 is not null then t.sums_ophc_01 else 0 end + ");
		sb.append("case when t.sums_ophc_02 is not null then t.sums_ophc_02 else 0 end + ");
		sb.append("case when t.sums_ophc_03 is not null then t.sums_ophc_03 else 0 end + ");
		sb.append("case when t.sums_ophc_04 is not null then t.sums_ophc_04 else 0 end + ");
		sb.append("case when t.sums_ophc_05 is not null then t.sums_ophc_05 else 0 end + ");
		sb.append("case when t.sums_ophc_06 is not null then t.sums_ophc_06 else 0 end + ");
		sb.append("case when t.sums_ophc_07 is not null then t.sums_ophc_07 else 0 end + ");
		sb.append("case when t.sums_ophc_08 is not null then t.sums_ophc_08 else 0 end ) <> 0 ");
		sb.append(" then ");
		sb.append("case when t.sums_iphc is not null and t.filename_send not like '%ORF%' then t.sums_iphc  else 0 end + ");
		sb.append("case when t.sums_ophc_01 is not null then t.sums_ophc_01 else 0 end + ");
		sb.append("case when t.sums_ophc_02 is not null then t.sums_ophc_02 else 0 end + ");
		sb.append("case when t.sums_ophc_03 is not null then t.sums_ophc_03 else 0 end + ");
		sb.append("case when t.sums_ophc_04 is not null then t.sums_ophc_04 else 0 end + ");
		sb.append("case when t.sums_ophc_05 is not null then t.sums_ophc_05 else 0 end + ");
		sb.append("case when t.sums_ophc_06 is not null then t.sums_ophc_06 else 0 end + ");
		sb.append("case when t.sums_ophc_07 is not null then t.sums_ophc_07 else 0 end + ");
		sb.append("case when t.sums_ophc_08 is not null then t.sums_ophc_08 else 0 end ");
		sb.append("when t.sums_ophc is not null then t.sums_ophc end ");
		sb.append("else 0 ");
		sb.append("end as sumsHC, ");

		sb.append("case when t.deny_ae = 'C'  and t.code_id is null then (");
		sb.append("case when t.sums_opae is not null then t.sums_opae   else 0 end + ");
		sb.append("case when t.sums_ipnb is not null then t.sums_ipnb   else 0 end + ");
		sb.append("case when t.sums_ippuc is not null then t.sums_ippuc   else 0 end + ");
		sb.append("case when t.sums_ip3sss is not null then t.sums_ip3sss   else 0 end + ");
		sb.append("case when t.sums_ip7sss is not null then t.sums_ip7sss   else 0 end + ");
		sb.append("case when t.sums_carae is not null then t.sums_carae   else 0 end + ");
		sb.append("case when t.sums_caref is not null then t.sums_caref   else 0 end + ");
		sb.append("case when t.sums_caref_puc is not null then t.sums_caref_puc   else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as sumsAE, ");

		sb.append("case when t.deny_inst = 'C' and t.code_id is null  then (");
		sb.append("case when t.sums_opinst is not null then t.sums_opinst else 0 end + ");
		sb.append("case when t.sums_inst is not null then t.sums_inst else 0 end ) ");
		sb.append("end as sumsINST, ");


		sb.append("case when t.deny_ip = 'C' and t.code_id is null  then case when ( ");
		sb.append("case when t.sums_ipaec is not null then t.sums_ipaec   else 0 end +  ");
		sb.append("case when t.sums_ipaer is not null then t.sums_ipaer  else 0 end + ");
		sb.append("case when t.sums_ipinrgc is not null then t.sums_ipinrgc  else 0 end + "); 
		sb.append("case when t.sums_ipinrgr is not null then t.sums_ipinrgr  else 0 end + "); 
		sb.append("case when t.sums_ipinspsn is not null then t.sums_ipinspsn  else 0 end + "); 
		sb.append("case when t.sums_ipprcc is not null then t.sums_ipprcc  else 0 end + "); 
		sb.append("case when t.sums_ipprcc_puc is not null then t.sums_ipprcc_puc  else 0 end) <> 0 "); 
		sb.append("then "); 
		sb.append("case when t.sums_ipaec is not null then t.sums_ipaec   else 0 end + ");  
		sb.append("case when t.sums_ipaer is not null then t.sums_ipaer  else 0 end + "); 
		sb.append("case when t.sums_ipinrgc is not null then t.sums_ipinrgc  else 0 end + "); 
		sb.append("case when t.sums_ipinrgr is not null then t.sums_ipinrgr  else 0 end + "); 
		sb.append("case when t.sums_ipinspsn is not null then t.sums_ipinspsn  else 0 end +  ");
		sb.append("case when t.sums_ipprcc is not null then t.sums_ipprcc  else 0 end + "); 
		sb.append("case when t.sums_ipprcc_puc is not null then t.sums_ipprcc_puc  else 0 end "); 
		sb.append("when t.sums_ip is not null then ");
		sb.append("t.sums_ip end ");
		sb.append("else 0 "); 	
		sb.append("end as sumsIP, ");

		sb.append("case when t.deny_dmis = 'C' and t.code_id is null  then (");
		sb.append("case when t.sums_cataract is not null then t.sums_cataract  else 0 end +  ");
		sb.append("          case         when t.EXPENSE_AMT_PROV is not null then t.EXPENSE_AMT_PROV  else 0 end +  ");
		sb.append("           case        when t.EXPENSE_AMT_HOSP is not null then t.EXPENSE_AMT_HOSP   else 0 end +  ");
		sb.append("case when t.sums_catinst is not null then t.sums_catinst  else 0 end + ");
		sb.append("case when t.sums_dmisrc is not null then t.sums_dmisrc  else 0 end + ");
		sb.append("case when t.sums_rcuhosc is not null then t.sums_rcuhosc  else 0 end + ");
		sb.append("case when t.sums_rcuhosr is not null then t.sums_rcuhosr  else 0 end + ");
		sb.append("case when t.sums_llop is not null then t.sums_llop  else 0 end + ");
		sb.append("case when t.sums_llrgc is not null then t.sums_llrgc  else 0 end + ");
		sb.append("case when t.sums_llrgr is not null then t.sums_llrgr  else 0 end + ");
		sb.append("case when t.sums_lp is not null then t.sums_lp  else 0 end + ");
		sb.append("case when t.sums_stroke_drug is not null then t.sums_stroke_drug  else 0 end + ");
		sb.append("case when t.sums_dmidml is not null then t.sums_dmidml  else 0 end + ");
		sb.append("case when t.sums_fp is not null then t.sums_fp  else 0 end ) ");
		sb.append("else 0 ");
		sb.append("end as sumsDMIS, ");

		sb.append("      case when t.deny_op = 'C' and t.code_id is null  then");
		sb.append("           case when t.sums_op is not null then t.sums_op");
		sb.append("			else 0 ");
		sb.append("             end end as sumsOP,                   ");
		sb.append("      case when t.deny_pa = 'C' and t.code_id is null  then");
		sb.append("           case when t.sums_pa is not null then t.sums_pa");
		sb.append("			else 0 ");
		sb.append("             end end as sumsPA,  ");
		sb.append("      case when t.deny_ot = 'C' and t.code_id is null  then");
		sb.append("           case when t.sums_ot is not null then t.sums_ot");
		sb.append("			else 0 ");
		sb.append("             end end as sumsOT,    ");
		sb.append("      case when t.sums_drug is not null and t.code_id is null ");
		sb.append("            then t.sums_drug ");
		sb.append("			else 0 ");
		sb.append("            end as sumsDRUG, ");
		sb.append("t.fund_af as fund, t.parent_tran_id as parentTranId, t.code_id as codeId, t.deny_hc as hc, t.deny_ae as ae, t.deny_inst as inst, ");
		sb.append("t.deny_ip as ip, t.deny_dmis as dmis, t.DENY_OP as codeop, t.DENY_PA as codepa, t.DENY_OT as codeot, t.tran_id as tranId, t.eclaim_no as eclaimNo   ");
		sb.append(", cntORF  ");
		sb.append(" from ");
		sb.append("(select count(*) as cntORF from M_SEAMLESS_REP t, M_REGISTER h ");
		sb.append("where h.TRAN_ID = t.TRAN_ID ");
		sb.append("and trunc(h.DATE_SEND) >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and trunc(h.DATE_SEND) <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and t.rep='").append(repNo).append("' ");
		sb.append("and t.HCODE='").append(hospitalCode).append("' ");
		sb.append("and h.filename_send like '%ORF%'), ");

		sb.append(" M_SEAMLESS_REP t, M_REGISTER h ");
		sb.append("where h.TRAN_ID = t.TRAN_ID ");
		sb.append("and trunc(h.DATE_SEND) >= to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and trunc(h.DATE_SEND) <= to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and t.rep='").append(repNo).append("' ");
		sb.append("and t.HCODE='").append(hospitalCode).append("' ");
		sb.append("and h.FILENAME_SEND not like '%CS%' and h.FILENAME_SEND not like '%OFC%' "); 
		sb.append("and h.FILENAME_SEND not like '%SS%') ");
		sb.append("left join m_seamless_rep sr on parentTranId=sr.TRAN_ID and tran_id = sr.tran_id ");
		sb.append("order by id ");
		this.log.info("get details data from repno with sql query : "
				+ sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final List<SummaryTrackDetailsData> results = sql.list();
		session.close();
		this.log.info("end get details data from repno..........");

		return results;
	}
	

	@SuppressWarnings("unchecked")
	public List<SummaryTrackDetailsData> getDetailsByRepNo(final String hospitalCode, final String repNo, final Date fromDate,
			final Date toDate, final int start, final int end, final String searchBy, final String searchValue)throws Exception {
		this.log.info("get details data from repno : " + repNo
				+ " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select sss.*, m.sums_serviceitem as oldDischarge, ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where tran_id=sss.parentTranId AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) as oldCompensation,");
		sb.append("m.code_dw_hc as oldCodeDWHC, m.code_dw_inst as oldCodeDWINST, m.code_dw_ae as oldCodeDWAE, m.code_dw_ip as oldCodeDWIP, ");
		sb.append("m.code_dw_dmis as oldCodeDWDMIS, m.code_dw_ip_cslg as oldCodeDWIPCSLG, m.code_dw_op_cslg as oldCodeDWOPCSLG, ");
		sb.append("m.code_dw_pa_cslg as oldCodeDWPACSLG, m.code_dw_inst_cslg as oldCodeDWINSTCSLG, m.code_dw_ot_cslg as oldCodeDWOTCSLG,");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'DMISRC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSR')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsDMIS, ");
		sb.append("is_number(FN_SUMS_SUBFUND(m.TRAN_ID,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)) as oldSumsDRUG, ");
		/**
		 * --*** CS,LG *** --
		 */
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'IP')), 0)) ");
		sb.append(" else null end as oldSumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OP')), 0)) ");
		sb.append(" else null end as oldSumsOPCS,");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'PA')), 0)) ");
		sb.append(" else null end as oldSumsPACS,");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'INST')), 0))");
		sb.append(" else null end as oldSumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OT')), 0)) ");
		sb.append(" else null end as oldSumsOTCS,");
		sb.append("is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG'))as oldSumsDRUGCS ");
		sb.append(" from ");
		sb.append("(select rownum as id, M.TRAN_ID as tranId, m.parent_tran_id as parentTranId, a.hosp_rep_id as repNo, ");
		sb.append("m.eclaim_no as eclaimno, m.pid as pid, m.hn as hn, m.an as an, concat(concat(m.fname, ' '), m.lname) as fullname, ");
		sb.append("to_date(m.dateadm, 'yyyyMMdd') as admiddate, m.timeadm as admidtime, to_date(m.datedsc, 'yyyyMMdd') as dischargedate, m.timedsc as dischargetime, ");
		sb.append("case when m.person_type is not null then ");
		sb.append("   case when m.person_type='1' then 'OP' ");
		sb.append("        when m.person_type='2' then 'IP' end ");
		sb.append(" else ");
		sb.append("   case when (m.hn is not null and m.hn<>'-') and (m.an is null or m.an<>'-') then 'OP' ");
		sb.append("        when (m.hn is null or m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' ");
		sb.append("		   when (m.hn is not null and m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' end ");
		sb.append("end as personType, ");
		sb.append("to_number(replace(m.sums_serviceitem, ',', '')) as discharge, ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where m.tran_id=tran_id AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) as compensation, ");
		sb.append("m.rate as salarypercent, FN_SUMS_SALARY(tran_id) as salaryamount, fn_sums_charge_rule(tran_id) as chargeRule, ");
		sb.append("case when m.filename_send like '%ORF%' then 'Y' else '' end as orf, ");
		sb.append("case when m.filename_send like '%APPEAL%' then 'A' ");
		sb.append(" else ");
		sb.append("   case when m.code_id is null then 'P' ");
		sb.append("        when m.code_id='DENY' then 'ND' ");
		sb.append("   else 'N' ");
		sb.append("end end as status, ");
		sb.append("to_number(replace(fn_sums_oprefer_hmain(m.tran_id), ',', '')) as orfHMain, ");
		sb.append("to_number(replace(fn_sums_oprefer_nhso(m.tran_id), ',', '')) as orfNHSO, ");
		sb.append("a.hospital_id as hospitalCode, fn_get_hosp_name(a.hospital_id) as hospitalName, ");
		sb.append("m.code_id as codeId, m.code_dw_hc as codeDWHC, m.code_dw_inst as codeDWINST, m.code_dw_ae as codeDWAE, ");
		sb.append("m.code_dw_ip as codeDWIP, m.code_dw_dmis as codeDWDMIS, m.code_dw_ip_cslg as codeDWIPCSLG, ");
		sb.append("m.code_dw_op_cslg as codeDWOPCSLG, m.code_dw_pa_cslg as codeDWPACSLG, m.code_dw_inst_cslg as codeDWINSTCSLG, ");
		sb.append("m.code_dw_ot_cslg as codeDWOTCSLG, ");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'DMISRC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSR')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsDMIS, ");
		sb.append("is_number(FN_SUMS_SUBFUND(m.TRAN_ID,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)) as sumsDRUG, ");
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'IP')), 0)) ");
		sb.append(" else null end as sumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OP')), 0)) ");
		sb.append(" else null end as sumsOPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'PA')), 0)) ");
		sb.append(" else null end as sumsPACS, ");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'INST')), 0)) ");
		sb.append(" else null end as sumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OT')), 0)) ");
		sb.append(" else null end as sumsOTCS, ");
		sb.append("is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG'))as sumsDRUGCS ");
		sb.append(" from  m_register m join m_upload a ");
		sb.append(" on m.filename_send=a.filename ");
		sb.append(" and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		
		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		
		sb.append(") sss left join m_register m on m.tran_id=sss.parentTranId ");
		sb.append("where sss.id>").append(start).append(" and sss.id<=").append(end);
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryTrackDetailsData.class);
		final List<SummaryTrackDetailsData> results = sql.list();
		session.close();
		this.log.info("end get details data from repno..........");

		return results;
	}

	public BigDecimal getSumsTotal(final String hospitalCode, final String repNo, 
			final Date fromDate, final Date toDate, final String searchBy, final String searchValue)throws Exception {
		this.log.info("get sums act amount details data from repno : " + repNo + " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		
		sb.append("select sum(to_number(replace(m.sums_serviceitem, ',', ''))) as sumsTotal ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		
		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}
	
	public BigDecimal getSumsActAmount(final String hospitalCode, final String repNo, 
			final Date fromDate, final Date toDate, final String searchBy, final String searchValue)throws Exception {
		this.log.info("get sums act amount details data from repno : " + repNo + " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		
		sb.append("select sum(nvl((select sum(act_amt) from m_sum_subfund where m.tran_id=tran_id ");
		sb.append("AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0)) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		
		if(ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			if(ESearchByStatus.PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id is null ");
				sb.append("and m.filename_send not like '%APPEAL%' ");
			}else if(ESearchByStatus.NOT_PASS.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
			}else if(ESearchByStatus.NOT_PASS_C.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.code_id='DENY' ");
			}else if(ESearchByStatus.APPEAL.getSearchBy().equalsIgnoreCase(searchValue)){
				sb.append("and m.filename_send like '%APPEAL%' ");
			}
		}else if(searchBy != null && searchBy.length()>0){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}
	
	public BigDecimal getCountPerson(final String hospitalCode, final String repNo, 
			final Date fromDate, final Date toDate, final String searchBy, final String searchValue, 
			final ESearchByStatus status)throws Exception {
		this.log.info("get sums act amount details data from repno : " + repNo + " and hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();

		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hosp_rep_id='").append(repNo).append("' ");
		sb.append("and a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		sb.append("and a.hosp_rep_id is not null ");
		if(ESearchByStatus.APPEAL == status){
			sb.append("and m.filename_send like '%APPEAL%' ");
		}else if(ESearchByStatus.NOT_PASS == status){
			sb.append("and m.code_id<>'DENY' and m.code_id is not null ");
		}else if(ESearchByStatus.NOT_PASS_C == status){
			sb.append("and m.code_id='DENY' ");
		}else if(ESearchByStatus.PASS == status){
			sb.append("and m.code_id is null ");
			sb.append("and m.filename_send not like '%APPEAL%' ");
		}
		
		if((searchBy != null && searchBy.length()>0) && !ESearchBy.STATUS.getSearchBy().equalsIgnoreCase(searchBy)){
			sb.append("and m.").append(searchBy).append("='").append(searchValue).append("' ");
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}
}
